<?php
/*========================================= JooSam =========================================/
/																							/
/	Extention:		JooSam																	/
/	Created by:		Broadcastweb.eu - Paules												/
/	Site:			http://www.broadcastweb.eu												/
/	Email:			support@broadcastweb.eu													/
/	Copyright:		2011 - 2012 (c) Broadcastweb.eu											/
/	License:		CC BY-NC-ND 3.0 ~ http://creativecommons.org/licenses/by-nc-nd/3.0/		/
/	File Version:	0003																	/
/																							/
/========================================== JooSam ========================================*/

// Joomla security
defined('_JEXEC') or die;

class SamdbHelper{

	public function samdb($type, $dbid, $rid = 0){

		$conf = JooSamHelper::settings('mysql', $dbid);
		$error = 0;
		$errno = '';
		$data = '';
		$load = JRequest::getVar('load', '50');
		$load = (int)$load;
		$host = $conf['server'];
		$port = (int)$conf['port'];
		$user = $conf['user'];
		$pass = $conf['pass'];
		$db = $conf['db'];

		$mysqli = new mysqli($host, $user, $pass, $db, $port);
		
		if(mysqli_connect_error()){
			$error = $error+1;
			$errno = 'Mysql Error: '.mysqli_connect_errno().' - '.mysqli_connect_error();
		}

		if($error > 0){
			$result = array('status' => 'error', 'data' => $errno);
			return $result;
		}
		// Get all categories
		else if($type == 'getCategory'){
			$query = 'SELECT * FROM category';
			if($sql = $mysqli->query($query)){
				while($row = $sql->fetch_assoc()){
					$data[] = $row;
				}
				$sql->free();
				$mysqli->close();
				if($data != ''){
					$result = array('status' => 'valid', 'data' => $data);
					return $result;
				}
				else{
					$result = array('status' => 'error', 'data' => 'nodata');
					return $result;
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'noquery');
				return $result;
			}
		}
		// Get full songlist
		else if($type == 'getSonglist'){
			$mainframe = JFactory::getApplication();
			$limitstart = JRequest::getVar('limitstart', 0, '', 'int');
			$limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
			$jsmsearch = JRequest::getVar('jsm_lettersearch', 'nolet');
			if($jsmsearch != 'nolet'){
				$qsear = $jsmsearch.'%';
			}
			else{
				$qsear = "%";
			}
			if($limit != 0){
				$query = "	SELECT s.ID, s.songtype, s.artist, s.title, s.picture, c.name
						FROM songlist AS s, category AS c, categorylist AS l
						WHERE (l.songID = s.ID)
						AND (l.categoryID = c.ID)
						AND (s.artist LIKE '".$qsear."')
						ORDER BY s.artist ASC, s.title ASC
						LIMIT $limitstart, $limit
					";
			}
			else{
				$query = "	SELECT s.ID, s.songtype, s.artist, s.title, s.picture, c.name
						FROM songlist AS s, category AS c, categorylist AS l
						WHERE (l.songID = s.ID)
						AND (l.categoryID = c.ID)
						AND (s.artist LIKE '".$qsear."')
						ORDER BY s.artist ASC, s.title ASC
					";
			}
			if($sql = $mysqli->query($query)){
				while($row = $sql->fetch_assoc()){
					$data[] = $row;
				}
				$sql->free();
				$mysqli->close();
				if($data != ''){
					$result = array('status' => 'valid', 'data' => $data);
					return $result;
				}
				else{
					$result = array('status' => 'error', 'data' => $query);
					return $result;
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'noquery');
				return $result;
			}
		}
		// Count songs to know the pages
		else if($type == 'countSonglist'){
			$jsmsearch = JRequest::getVar('jsm_lettersearch', 'nolet');
			if($jsmsearch != 'nolet'){
				$qsear = $jsmsearch.'%';
				$query = "	SELECT ID FROM songlist WHERE artist LIKE '".$qsear."'";
			}
			else{
				$query = "	SELECT ID FROM songlist";
			}
			
			if($sql = $mysqli->query($query)){
				$row = $sql->num_rows;
				$mysqli->close();
				if($row != ''){
					//$data = ceil($row / $load);
					$data = $row;
					$result = array('status' => 'valid', 'data' => $data);
					return $result;
				}
				else{
					$result = array('status' => 'error', 'data' => 'nocount');
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'noquery');
				return $result;
			}
		}
		// Update songlist
		else if($type == 'updateSongDate'){

			$data_songid = JRequest::getVar('song_id', 'jsmerror');
			$data_songtype = JRequest::getVar('song_type', 'jsmerror');
			$data_title = JRequest::getVar('song_title', 'jsmerror');
			$data_title = str_replace('"', "'", $data_title);
			$data_artist = JRequest::getVar('song_artist', 'jsmerror');
			$data_artist = str_replace('"', "'", $data_artist);
			$data_album = JRequest::getVar('song_album', 'jsmerror');
			$data_album = str_replace('"', "'", $data_album);
			$data_trackno = JRequest::getVar('song_trackno', 'jsmerror');
			$data_albumyear = JRequest::getVar('song_albumyear', 'jsmerror');
			$data_label = JRequest::getVar('song_label', 'jsmerror');
			$data_label = str_replace('"', "'", $data_label);
			$data_composer = JRequest::getVar('song_composer', 'jsmerror');
			$data_composer = str_replace('"', "'", $data_composer);
			$data_website = JRequest::getVar('song_website', 'jsmerror');
			$data_website = str_replace('"', "'", $data_website);
			$data_buycd = JRequest::getVar('song_buycd', 'jsmerror');
			$data_buycd = str_replace('"', "'", $data_buycd);
			$data_genre = JRequest::getVar('song_genre', 'jsmerror');
			$data_genre = str_replace('"', "'", $data_genre);
			$data_mood = JRequest::getVar('song_mood', 'jsmerror');
			$data_weight = JRequest::getVar('song_weight', 'jsmerror');
			$data_rating = JRequest::getVar('song_rating', 'jsmerror');
			$data_lyrics = JRequest::getVar('song_lyrics', 'jsmerror');
			$data_lyrics = str_replace('"', "'", $data_lyrics);
			$data_info = JRequest::getVar('song_info', 'jsmerror');
			$data_info = str_replace('"', "'", $data_info);
			$data_count_played = JRequest::getVar('song_count_played', 'jsmerror');
			$data_count_requested = JRequest::getVar('song_count_requested', 'jsmerror');
			$data_playlimit_count = JRequest::getVar('song_playlimit_count', 'jsmerror');

			$query = "none";
			$first = 0;
			if($data_songid != 'jsmerror' AND $data_songid != ''){
				$query = "UPDATE songlist SET ";
				// songtype
				if($data_songtype != 'jsmerror' AND $data_songtype != ''){$query .= "songtype = \"$data_songtype\""; $first = 1; }
				// weight
				if($data_weight != 'jsmerror' AND $data_weight != ''){
					if($first == 1){ $query .= ", weight = \"$data_weight\""; }
					else{ $query .= "weight = \"$data_weight\""; $first = 1; }
				}
				// artist
				if($data_artist != 'jsmerror' AND $data_artist != ''){
					if($first == 1){ $query .= ", artist = \"$data_artist\""; }
					else{ $query .= "artist = \"$data_artist\""; $first = 1; }
				}
				// title
				if($data_title != 'jsmerror' AND $data_title != ''){
					if($first == 1){ $query .= ", title = \"$data_title\""; }
					else{ $query .= "title = \"$data_title\""; $first = 1; }
				}
				// album
				if($data_album != 'jsmerror' AND $data_album != ''){
					if($first == 1){ $query .= ", album = \"$data_album\""; }
					else{ $query .= "album = \"$data_album\""; $first = 1; }
				}
				// label
				if($data_label != 'jsmerror' AND $data_label != ''){
					if($first == 1){ $query .= ", label = \"$data_label\""; }
					else{ $query .= "label = \"$data_label\""; $first = 1; }
				}
				// trackno
				if($data_trackno != 'jsmerror' AND $data_trackno != ''){
					if($first == 1){ $query .= ", trackno = \"$data_trackno\""; }
					else{ $query .= "trackno = \"$data_trackno\""; $first = 1; }
				}
				// composer
				if($data_composer != 'jsmerror' AND $data_composer != ''){
					if($first == 1){ $query .= ", composer = \"$data_composer\""; }
					else{ $query .= "composer = \"$data_composer\""; $first = 1; }
				}
				// albumyear
				if($data_albumyear != 'jsmerror' AND $data_albumyear != ''){
					if($first == 1){ $query .= ", albumyear = \"$data_albumyear\""; }
					else{ $query .= "albumyear = \"$data_albumyear\""; $first = 1; }
				}
				// genre
				if($data_genre != 'jsmerror' AND $data_genre != ''){
					if($first == 1){ $query .= ", genre = \"$data_genre\""; }
					else{ $query .= "genre = \"$data_genre\""; $first = 1; }
				}
				// website
				if($data_website != 'jsmerror' AND $data_website != ''){
					if($first == 1){ $query .= ", website = \"$data_website\""; }
					else{ $query .= "website = \"$data_website\""; $first = 1; }
				}
				// buycd
				if($data_buycd != 'jsmerror' AND $data_buycd != ''){
					if($first == 1){ $query .= ", buycd = \"$data_buycd\""; }
					else{ $query .= "buycd = \"$data_buycd\""; $first = 1; }
				}
				// info
				if($data_info != 'jsmerror' AND $data_info != ''){
					if($first == 1){ $query .= ", info = \"$data_info\""; }
					else{ $query .= "info = \"$data_info\""; $first = 1; }
				}
				// lyrics
				if($data_lyrics != 'jsmerror' AND $data_lyrics != ''){
					if($first == 1){ $query .= ", lyrics = \"$data_lyrics\""; }
					else{ $query .= "lyrics = \"$data_lyrics\""; $first = 1; }
				}
				// mood
				if($data_mood != 'jsmerror' AND $data_mood != ''){
					if($first == 1){ $query .= ", mood = \"$data_mood\""; }
					else{ $query .= "mood = \"$data_mood\""; $first = 1; }
				}
				// rating
				if($data_rating != 'jsmerror' AND $data_rating != ''){
					if($first == 1){ $query .= ", rating = \"$data_rating\""; }
					else{ $query .= "rating = \"$data_rating\""; $first = 1; }
				}
				// count_played
				if($data_count_played != 'jsmerror' AND $data_count_played != ''){
					if($first == 1){ $query .= ", count_played = \"0\""; }
					else{ $query .= "count_played = 0"; $first = 1; }
				}
				// count_requested
				if($data_count_requested != 'jsmerror' AND $data_count_requested != ''){
					if($first == 1){ $query .= ", count_requested = \"0\""; }
					else{ $query .= "count_requested = \"0\""; $first = 1; }
				}
				// playlimit_count
				if($data_playlimit_count != 'jsmerror' AND $data_playlimit_count != ''){
					if($first == 1){ $query .= ", playlimit_count = \"0\""; }
					else{ $query .= "playlimit_count = \"0\""; $first = 1; }
				}
				if($first == 1){ $query .= " WHERE ID = \"$data_songid\""; }
				else{ $first = -1; }
			}
			if($query != 'none' and $query != '' and $first > 0){
				$mysqli->query($query);
				$row = $mysqli->affected_rows;
				if($row != ''){
					$data = $row;
					$result = array('status' => 'valid', 'data' => $data.$mysqli->error);
					$mysqli->close();
					return $result;
				}
				else{
					$result = array('status' => 'valid', 'data' => JText::_('COM_JOOSAM_ERROR_MYSQL_NOUPDATENEED'));
					return $result;
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'norow');
				return $result;
			}
		}
		// Reset Play Counter
		else if($type == 'resetPlayCount'){
			$query = "UPDATE songlist SET count_played=0 WHERE count_played > 0";
			$mysqli->query($query);
			$row = $mysqli->affected_rows;
			$mysqli->close();
			if($row != ''){
				$data = (int)$row;
				if($data >= 0){
					$result = array('status' => 'valid', 'data' => $data);
				}
				else{
					$result = array('status' => 'error', 'data' => $mysqli->error);
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'norow');
			}
			return $result;
		}
		// Reset Request Counter
		else if($type == 'resetRequestCount'){
			$query = "UPDATE songlist SET count_requested=0 WHERE count_requested > 0";
			$mysqli->query($query);
			$row = $mysqli->affected_rows;
			$mysqli->close();
			if($row != ''){
				$data = (int)$row;
				if($data >= 0){
					$result = array('status' => 'valid', 'data' => $data);
				}
				else{
					$result = array('status' => 'error', 'data' => $mysqli->error);
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'norow');
			}
			return $result;
		}
		// Reset Performence data
		else if($type == 'resetPerfemCount'){
			$query = "UPDATE songlist SET count_performances=0 WHERE count_performances > 0";
			$mysqli->query($query);
			$row = $mysqli->affected_rows;
			$mysqli->close();
			if($row != ''){
				$data = (int)$row;
				if($data >= 0){
					$result = array('status' => 'valid', 'data' => $data);
				}
				else{
					$result = array('status' => 'error', 'data' => $mysqli->error);
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'norow');
			}
			return $result;
		}
		// delete historylist
		else if($type == 'delFullHistory'){
			$query = "DELETE FROM historylist";
			$mysqli->query($query);
			$row = $mysqli->affected_rows;
			$mysqli->close();
			if($row != ''){
				$data = (int)$row;
				if($data >= 0){
					$result = array('status' => 'valid', 'data' => $data);
				}
				else{
					$result = array('status' => 'error', 'data' => $mysqli->error);
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'norow');
			}
			return $result;
		}
		// delete Requestlist
		else if($type == 'delFullReqlist'){
			$query = "DELETE FROM requestlist";
			$mysqli->query($query);
			$row = $mysqli->affected_rows;
			$mysqli->close();
			if($row != ''){
				$data = (int)$row;
				if($data >= 0){
					$result = array('status' => 'valid', 'data' => $data);
				}
				else{
					$result = array('status' => 'error', 'data' => $mysqli->error);
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'norow');
			}
			return $result;
		}
		// delete Requestlist
		else if($type == 'fixSongTypeData'){
			$query = "UPDATE songlist SET songtype='S' WHERE songtype NOT IN ('S','I','P','J','A','N','V','X','C');";
			$mysqli->query($query);
			$row = $mysqli->affected_rows;
			if($row != ''){
				$data = (int)$row;
				if($data >= 0){
					$mysqli->close();
					$result = array('status' => 'valid', 'data' => $data);
				}
				else{
					$result = array('status' => 'error', 'data' => $mysqli->error);
					$mysqli->close();
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'norow');
			}
			return $result;
		}
		// delete Requestlist
		else if($type == 'updateRequestData' and $rid > 0){

			$data_request_name = JRequest::getVar('request_song_name', 'jsmerror');
			$data_request_name = str_replace('"', "'", $data_request_name);
			$data_request_msg = JRequest::getVar('request_song_msg', 'jsmerror');
			$data_request_msg = str_replace('"', "'", $data_request_msg);

			$query = "UPDATE requestlist
						SET name=\"$data_request_name\", msg=\"$data_request_msg\"
						WHERE ID=\"$rid\" ; ";
			$mysqli->query($query);
			$row = $mysqli->affected_rows;
			if($row != ''){
				$data = (int)$row;
				if($data >= 0){
					$mysqli->close();
					$result = array('status' => 'valid', 'data' => $data);
				}
				else{
					$result = array('status' => 'error', 'data' => $mysqli->error);
					$mysqli->close();
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'norow');
			}
			return $result;
		}
		// Get info about 1 song
		else if($type == 'getSongInfo'){
			$songid = JRequest::getVar('sid');
			$query = "	SELECT s.*, c.ID AS category_id, c.name AS category_name
							FROM songlist AS s, category AS c, categorylist AS l
							WHERE (s.ID = $songid)
								AND (l.songID = s.ID)			
								AND (l.categoryID = c.ID)
					";
			if($sql = $mysqli->query($query)){
				while($row = $sql->fetch_assoc()){
					$data[] = $row;
				}
				$sql->free();
				$mysqli->close();
				if($data != ''){
					$result = array('status' => 'valid', 'data' => $data);
					return $result;
				}
				else{
					$result = array('status' => 'error', 'data' => $query);
					return $result;
				}
			}
			else{
				$result = array('status' => 'error', 'data' => 'noquery');
				return $result;
			}
		}
		else{
			$result = array('status' => 'error', 'data' => 'badcall');
			return $result;
		}
	}
}